﻿
using MySql.Data.MySqlClient;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using NB.MGJ.Model.SystemEntity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NB.MGJ.Service
{
    /// <summary>
    /// 通用
    /// 
    /// </summary>
    public class CommonService
    {
        private BaseRepository _baseRepository = new BaseRepository();

        /// <summary>
        /// 通过父级ID获取市或区或县
        /// </summary>
        public List<PCRModel> GetPCRModel(int parentID)
        {
            var strSql = " select * from Sys_Region where parentID=@parentID ";
            MySqlParameter[] parameters = { new MySqlParameter("@parentID", parentID) };
            IEnumerable<PCRModel> _query = _baseRepository.ExecuteSqlCollection<PCRModel>(strSql, parameters);
            return _query.ToList();

        }
        public List<PCRModel> GetPCRModel(string Ids)
        {

            string sql = string.Empty;
            string[] parids = Ids.TrimEnd(',').Split(',');
            Ids = string.Empty;
            foreach (string str in parids)
                Ids += "'" + str + "',";
            Ids = Ids.TrimEnd(',');
            sql = @"select * from Sys_Region where 1=1 and  codeID in ({0})";
            sql = string.Format(sql, Ids);
            IEnumerable<PCRModel> _query = _baseRepository.ExecuteSqlCollection<PCRModel>(sql, null);
            return _query.ToList();

        }
        public List<PCRModel> GetPCRModelAll(string parentID)
        {
            if (string.IsNullOrWhiteSpace(parentID))
            {
                return null;
            }
            var strSql = " select * from Sys_Region where parentID in (" + parentID + ")";
            IEnumerable<PCRModel> _query = _baseRepository.ExecuteSqlCollection<PCRModel>(strSql);
            return _query.ToList();

        }

        public List<Sys_Menu> GetAllMenuInfoList(string sql)
        {
            return _baseRepository.ExecuteSqlCollection<Sys_Menu>(sql).ToList();
        }

        #region 添加导入文件
        public int uploadImportFile(sys_importfile model)
        {
            return _baseRepository.InsertReturnID<sys_importfile>(model);
        }
        #endregion

        #region 扫码记录添加
        public void ScanCodeHistoryAdd(string[] codes, int? userId, int codeType, string uniqueIdentifier = null)
        {

            if (codes == null || (!userId.HasValue && string.IsNullOrWhiteSpace(uniqueIdentifier))) { return; }

            BaseExecute _baseExecute = new BaseExecute();
            string _resultUpdateSql = string.Empty, _resultInsertSql = string.Empty;

            using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandType = CommandType.Text;
                    string _resultsql = string.Empty;
                    for (int i = 0; i < codes.Length; i++)
                    {
                        string _sql = string.Format("SELECT ID,ScanCount FROM scancodehistory WHERE CodeType = {0}   ", codeType);
                        string _code = codes[i];
                        if (!string.IsNullOrWhiteSpace(_code))
                        {
                            _sql += " AND Code = " + _code;
                            if (userId.HasValue)
                            {
                                _sql += " AND UserId = " + userId.Value;
                            }
                            if (!string.IsNullOrWhiteSpace(uniqueIdentifier))
                            {
                                _sql += string.Format(" AND UniqueIdentifier = '{0}' ", uniqueIdentifier);
                            }

                            cmd.CommandText = _sql;
                            MySqlDataReader reader = cmd.ExecuteReader();
                            int _id = 0, _scancount = 0;
                            if (reader.Read())
                            {
                                _id = (int)reader["ID"];
                                _scancount = (int)reader["ScanCount"];

                            }
                            if (_id != 0)
                            {
                                _resultUpdateSql += String.Format("UPDATE scancodehistory SET ScanCount = {0},ScanDate='{2}' WHERE ID = {1};", _scancount += 1, _id, DateTime.Now);
                            }
                            else
                            {
                                _resultInsertSql += _baseExecute.GetInsertSQL<ScanCodeHistory>(new ScanCodeHistory
                                {
                                    Code = _code,
                                    CodeType = codeType,
                                    ScanCount = 1,
                                    UserId = userId ?? null,
                                    UniqueIdentifier = uniqueIdentifier,
                                    ScanDate = DateTime.Now
                                }).ToString() + ";";
                            }
                            reader.Close();
                        }
                    }
                    cmd.CommandText = _resultInsertSql.Replace("SELECT @@IDENTITY;;", "") + _resultUpdateSql;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion

        public DataStatisticsModel SystemDataStatistics(int userId, string enterpriseId, string unCode)
        {
            string _un_where = string.Empty, _data_where = string.Empty;
            if (!string.IsNullOrWhiteSpace(unCode))
            {
                _un_where = string.Format(" OR UniqueIdentifier = '{0}'", unCode);
            }
            _data_where = string.Format(" ScanDate >= '{0}' And ScanDate < '{1}'  ", DateTime.Now.ToString("yyyy/MM/dd"), DateTime.Now.AddDays(1).ToString("yyyy/MM/dd"));

            string _sql = string.Format(@"SELECT
	       (SELECT SUM(ScanCount)	FROM	scancodehistory	WHERE	UserId = {0}	{1}	) HScanCount,
	       (SELECT SUM(ScanCount)	FROM	scancodehistory	WHERE	(	UserId = {0} {1})
		           AND {2}
	       ) TScanCount,
	       (SELECT COUNT(ID) FROM	marketing_activity WHERE  ChangeLoginID = {0}	) ActivityCount,
           (SELECT SUM(Gen_Code_Nums) FROM codemanage_gencode  WHERE EnterpriseId = {3}) GenCodeCount,
           (SELECT SUM(Gen_Code_Nums) FROM codemanage_gencode  WHERE EnterpriseId = {3} AND CodeType LIKE '%2%') AntifakeCount,
           (SELECT SUM(Gen_Code_Nums) FROM codemanage_gencode  WHERE EnterpriseId = {3} AND CodeType LIKE '%1%') AntifleeingCount", userId, _un_where, _data_where, enterpriseId);
            return _baseRepository.ExecuteTextCollection<DataStatisticsModel>(_sql).FirstOrDefault();
        }
    }

   public class DataStatisticsModel
    {
        /// <summary>
        /// 历史扫码数
        /// </summary>
       private long? hscancount;
        public long HScanCount
        {
            get
            {
                if (!hscancount.HasValue)
                {
                    return 0;
                }
                return hscancount.Value;
            }
            set
            {
                hscancount = value;
            }
        }

        /// <summary>
        /// 今日扫码数
        /// </summary>
        private long? tscancount;
        public long TScanCount
        {
            get
            {
                if (!tscancount.HasValue)
                {
                    return 0;
                }
                return tscancount.Value;
            }
            set
            {
                tscancount = value;
            }
        }

        /// <summary>
        /// 活动数
        /// </summary>
        public long ActivityCount { get; set; }

        /// <summary>
        /// 生码数
        /// </summary>
        private long? gencodecount;
        public long GenCodeCount
        {
            get
            {
                if (!gencodecount.HasValue)
                {
                    return 0;
                }
                return gencodecount.Value;
            }
            set
            {
                gencodecount = value;
            }
        }

        /// <summary>
        /// 用于防伪数
        /// </summary>
        private long? antifakecount;
        public long AntifakeCount
        {
            get
            {
                if (!antifakecount.HasValue)
                {
                    return 0;
                }
                return antifakecount.Value;
            }
            set
            {
                antifakecount = value;
            }
        }

        /// <summary>
        /// 用于溯源数
        /// </summary>
        private long? antifleeingcount;
        public long AntifleeingCount
        {
            get
            {
                if (!antifleeingcount.HasValue)
                {
                    return 0;
                }
                return antifleeingcount.Value;
            }
            set
            {
                antifleeingcount = value;
            }
        }
    }
}
